﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using WebApplication1.Common;
using WebApplication1.Models;

namespace WebApplication1.BLL
{
    public class UserInfoBLL
    {
        /// <summary>
        /// 根据账号、密码查询用户
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="pwd"></param>
        /// <param name="checkLogin">0不存在该用户，1表示登录成功，2密码错误</param>
        /// <returns></returns>

        public DataTable GetUser(string userName, string pwd, out int checkLogin, out string userId)
        {
            string sql = "SELECT TOP 1 [jzId],[stuId],[jzName],m.[sex],[jzNo],[password],[phone],[sfzNo],[minZu],m.[schoolId],m.[headImgURL],[address],[guanxi],t.classId FROM [dbo].[T_JiaZhang] as m left join [dbo].[T_Student] as t on t.studentId=m.stuId where [phone]=@phone";
            string pwdBM = DESencodingTxt.DESencrypt(pwd);//加密传过来的密码
            SqlParameter[] pames = new SqlParameter[] {
                new SqlParameter("@phone",userName)
            };
            DataTable dt = SQLHepler.GetDataSet(sql, pames).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                var item = dt.Rows[0];
                string stuId = item["stuId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["stuId"]);
                userId = item["jzId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["jzId"]);
                string password = item["password"] == System.DBNull.Value ? "" : Convert.ToString(item["password"]);
                if (string.IsNullOrEmpty(password))
                {
                    checkLogin = 2;//密码错误
                }
                else
                {
                    if (password.Equals(pwdBM))
                    {
                        checkLogin = 1;//登录成功
                    }
                    else
                    {
                        checkLogin = 2;//密码错误
                    }
                }
            }
            else
            {
                checkLogin = 0;//账号不存在
                userId = "";
            }
            return dt;
        }

        /// <summary>
        /// 查询最新的版本
        /// </summary>
        /// <returns></returns>
        public DataTable GetAppNewest()
        {
            string sql = "SELECT TOP 1 [id],[versionName],[versionNo],[creatTime],[androidAppURL],[type] FROM [dbo].[T_Versions] where type=1 order by [creatTime] desc ";
            DataTable dt = SQLHepler.GetDataSet(sql).Tables[0];
            return dt;
        }

        /// <summary>
        /// 记录登录日志
        /// </summary>
        /// <param name="userId">登录用户id</param>
        /// <returns></returns>
        public int AddLoginLog(string userId)
        {
            string sql = "INSERT INTO [dbo].[T_LoginLog]([id],[loginUserId],[loginTime],[type]) VALUES(@id,@loginUserId,@loginTime,@type)";
            SqlParameter[] parmaes =
            {
                new SqlParameter("@id",Guid.NewGuid().ToString()),
                new SqlParameter("@loginUserId",userId),
                new SqlParameter("@loginTime",DateTime.Now),
                new SqlParameter("@type","1"),
            };
            int count = SQLHepler.ExecuteSql(sql, parmaes);
            return count;
        }

        /// <summary>
        /// 根据主键查询用户id
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public JiaZhang GetJiaZhang(string userId)
        {
            JiaZhang model = new JiaZhang();
            string sql = "SELECT [jzId],[stuId],[jzName],[sex],[jzNo],[password],[phone],[sfzNo],[minZu],[email],[jiGuan],[schoolId],[headImgURL],[address],[guanxi],[sDateYMD000] FROM [dbo].[T_JiaZhang] where [jzId]=@jzId";
            DataTable dt = SQLHepler.GetDataSet(sql, new SqlParameter("@jzId", userId)).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                var item = dt.Rows[0];
                model.jzId = item["jzId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["jzId"]);
                model.stuId = item["stuId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["stuId"]);
                model.jzName = item["jzName"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["jzName"]);
                model.sex = item["sex"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["sex"]);
                model.jzNo = item["jzNo"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["jzNo"]);
                model.password = item["password"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["password"]);
                model.phone = item["phone"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["phone"]);
                model.sfzNo = item["sfzNo"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["sfzNo"]);
                model.minZu = item["minZu"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["minZu"]);
                model.email = item["email"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["email"]);
                model.jiGuan = item["jiGuan"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["jiGuan"]);
                model.schoolId = item["schoolId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["schoolId"]);
                model.headImgURL = item["headImgURL"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["headImgURL"]);
                model.address = item["address"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["address"]);
                model.guanxi = item["guanxi"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["guanxi"]);
                model.sDateYMD000 = item["sDateYMD000"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["sDateYMD000"]);
            }
            return model;
        }

        /// <summary>
        /// 根据用户id查询未读的消息记录个数
        /// </summary>
        /// <returns></returns>
        public int GetNoReadMsg(string userId)
        {
            string sql = "SELECT count(1) FROM[dbo].[T_Jiaoliu] where[receivePersonId] =@userId and[readState] = '0'";
            object val = SQLHepler.GetSingleValue(sql, new SqlParameter("@userId", userId));
            if (val != null)
            {
                return Convert.ToInt32(val);
            }
            return 0;
        }

        /// <summary>
        /// 提交建议
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="word"></param>
        /// <returns></returns>
        public int AddSuggest(string userId, string word)
        {
            string sql = "INSERT INTO [dbo].[T_Feedback]([userId],[suggest],[creatTime]) VALUES(@userId,@suggest,@creatTime)";
            SqlParameter[] parames = {
                new SqlParameter("@userId",userId),
                new SqlParameter("@suggest",word),
                new SqlParameter("@creatTime",DateTime.Now),
            };
            return SQLHepler.ExecuteSql(sql, parames);
        }

        /// <summary>
        /// 根据用户id修改密码
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public int UpdateJiaZhangPwd(string userId, string pwd)
        {
            string sql = "update [dbo].[T_JiaZhang] set [password]=@password where [jzId]=@userId";
            string pwd_bm = Common.DESencodingTxt.DESencrypt(pwd);
            SqlParameter[] parames = {
                new SqlParameter("@userId",userId),
                new SqlParameter("@password",pwd_bm)
            };
            return SQLHepler.ExecuteSql(sql, parames);
        }

        /// <summary>
        /// 根据学生id修改性别和生日
        /// </summary>
        /// <param name="stuId"></param>
        /// <param name="sex"></param>
        /// <param name="bornDate"></param>
        /// <returns></returns>
        public int UpdateStudent(string stuId, string sex, string bornDate)
        {
            string sql = "";
            SqlParameter[] parmaes;
            if (string.IsNullOrEmpty(sex) == false)
            {
                sql = "UPDATE [dbo].[T_Student] SET [sex] = @sex WHERE [studentId] = @studentId";
                parmaes = new SqlParameter[]{
                    new SqlParameter("@studentId", stuId),
                    new SqlParameter("@sex", sex)
                };
            }
            else
            {
                if (string.IsNullOrEmpty(bornDate))
                {
                    bornDate = DateTime.Now.ToString("yyyy-MM-dd");
                }
                sql = "UPDATE [dbo].[T_Student] SET [bornDate] = @bornDate WHERE [studentId] = @studentId";
                parmaes = new SqlParameter[]{
                    new SqlParameter("@studentId", stuId),
                    new SqlParameter("@bornDate", bornDate)
                };
            }
            return SQLHepler.ExecuteSql(sql, parmaes);
        }

        /// <summary>
        /// 修改宝宝头像
        /// </summary>
        /// <param name="stuId"></param>
        /// <param name="headImgPath"></param>
        /// <returns></returns>
        public int UpdateStudentImg(string stuId, string headImgPath)
        {
            string sql = "UPDATE [dbo].[T_Student] SET [headImgURL] = @headImgURL WHERE [studentId] = @studentId";
            SqlParameter[] parames = { new SqlParameter("@studentId", stuId), new SqlParameter("@headImgURL", headImgPath) };
            return SQLHepler.ExecuteSql(sql, parames);
        }

        /// <summary>
        /// 修改用户头像
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="headImgPath"></param>
        /// <returns></returns>
        public int UpdateUserImg(string userId, string headImgPath)
        {
            string sql = "UPDATE [dbo].[T_JiaZhang] SET [headImgURL] = @headImgURL WHERE [jzId] = @jzId";
            SqlParameter[] parames = { new SqlParameter("@jzId", userId), new SqlParameter("@headImgURL", headImgPath) };
            return SQLHepler.ExecuteSql(sql, parames);
        }

        /// <summary>
        /// 获取教师发来的消息，将阅读状态改为已读
        /// </summary>
        /// <param name="userId">用户id</param>
        /// <param name="teacherId">教师id</param>
        /// <param name="lastTime">大于该时间的消息</param>
        /// <returns></returns>
        public object GetTeacherMsg(string userId, string teacherId, string lastTime, string serverAddress)
        {
            string sql = "SELECT [id],[words],[sendPersonId],[receivePersonId],Convert(varchar(30),[creatTime],20) as creatTime,[schoolId],[readState] FROM [dbo].[T_Jiaoliu] where sendPersonId=@sendPersonId and receivePersonId=@receivePersonId and DATEDIFF(ss,@lastTime,Convert(varchar(30),[creatTime],20))>0 order by [creatTime] asc";
            SqlParameter[] parames = {
                new SqlParameter("@sendPersonId",teacherId),
                new SqlParameter("@receivePersonId",userId),
                new SqlParameter("@lastTime",lastTime)
            };
            DataTable dt = SQLHepler.GetDataSet(sql, parames).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                DataColumn col = new DataColumn("type");//消息类型
                dt.Columns.Add(col);//添加一列
                DataColumn col2 = new DataColumn("headImgURL");//老师头像
                dt.Columns.Add(col2);//添加一列                
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    var item = dt.Rows[i];
                    string msgId = item["id"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["id"]);
                    //修改时间格式
                    dt.Rows[i]["creatTime"] = Convert.ToDateTime(item["creatTime"]).ToString("yyyy/MM/dd HH:mm:ss");
                    //string words = item["words"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["words"]);
                    //if (string.IsNullOrWhiteSpace(words) == false)
                    //{
                    //    string wordsHtml = new SchoolBLL().ReplaceBqImg(words);
                    //    dt.Rows[i]["words"] = wordsHtml;
                    //}
                    string sendPersonId = item["sendPersonId"].GetType() == typeof(System.DBNull) ? "" : Convert.ToString(item["sendPersonId"]);
                    if (string.IsNullOrEmpty(sendPersonId) == false)
                    {
                        if (sendPersonId == teacherId)
                        {
                            dt.Rows[i]["type"] = "te";
                        }
                    }
                    //查询教师头像           
                    Teacher teacher = new SchoolBLL().GetTeacher(teacherId);
                    string teaHeadImgURL = "img/teacher.png";
                    if (teacher != null)
                    {
                        if (string.IsNullOrEmpty(teacher.headImgURL) == false)
                        {
                            teaHeadImgURL = serverAddress + teacher.headImgURL;
                        }
                    }
                    dt.Rows[i]["headImgURL"] = teaHeadImgURL;
                    //修改阅读状态
                    UpdateReadMsgState(msgId);
                }
            }
            return dt;
        }

        /// <summary>
        /// 根据消息id修改阅读状态为已读
        /// </summary>
        /// <param name="msgId"></param>
        /// <returns></returns>
        public int UpdateReadMsgState(string msgId)
        {
            string sql = " update [dbo].[T_Jiaoliu] set [readState]=1 where [id]=@id";
            return SQLHepler.ExecuteSql(sql, new SqlParameter("@id", msgId));
        }

    }
}
